﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Models;
using Eacademy.Domain.Entities;
using Eacademy.Library.Helpers;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;
using SubSonic.DataProviders;

namespace Eacademy.Domain.Repository
{

    public class SubjectRepository : ISubjectRepository
    {
        EacademyDB db = new EacademyDB();

        public List<SubjectCompactViewModel> GetSubjects()
        {
            int totalCount = 0;
            return GetSubjects(0, 0, 0, new SubjectListFilterViewModel(), out totalCount, false);
        }

        public List<SubjectCompactViewModel> GetSubjectsForStudent(int userid)
        {
            int totalCount = 0;
            return GetSubjects(0, 0, userid, new SubjectListFilterViewModel(), out totalCount, false);
        }


        public Boolean AddSubject(AdminSubjectsViewModel adminSubjectsViewModel)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Subject subject = new Subject();
                        subject.SubjectCode = adminSubjectsViewModel.SubjectCode;
                        subject.SubjectName = adminSubjectsViewModel.SubjectName;
                        subject.Save();

                        //var mentorIdList = adminSubjectsViewModel.MentorId.Split(',').Cast<Int32>().ToList();

                        var mentorIdList = adminSubjectsViewModel.MentorIdList.Split(',').ToList();

                        foreach (string mentorStringId in mentorIdList)
                        {
                            MentorSubjects mentorSubjects = new MentorSubjects();
                            mentorSubjects.SubjectId = subject.SubjectId;

                            int mentorId = 0;
                            Int32.TryParse(mentorStringId, out mentorId);

                            if (mentorId < 1)
                                continue;

                            mentorSubjects.MentorId = mentorId;
                            mentorSubjects.Save();
                        }
                        ts.Complete();
                    }

                    return true;
                }
            }
            catch (Exception ex)
            {
            }
            return false;
        }

        public Boolean UpdateSubject(AdminSubjectsViewModel adminSubjectsViewModel)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {

                        Subject subject = db.Select.From<Subject>().Where(SubjectsTable.SubjectIdColumn)
                            .IsEqualTo(adminSubjectsViewModel.SubjectId).ExecuteSingle<Subject>();
                        subject.SubjectCode = adminSubjectsViewModel.SubjectCode;
                        subject.SubjectName = adminSubjectsViewModel.SubjectName;
                        subject.SetIsNew(false);
                        subject.Save();

                        //var mentorIdList = adminSubjectsViewModel.MentorId.Split(',').Cast<Int32>().ToList();

                        MentorsSubjectBase.Delete(x => x.SubjectId == adminSubjectsViewModel.SubjectId);

                        var mentorIdList = adminSubjectsViewModel.MentorIdList.Split(',').ToList();

                        foreach (string mentorStringId in mentorIdList)
                        {
                            var mentorSubjects = new MentorSubjects();
                            mentorSubjects.SubjectId = subject.SubjectId;

                            int mentorId = 0;
                            Int32.TryParse(mentorStringId, out mentorId);

                            if (mentorId < 1)
                                continue;

                            mentorSubjects.MentorId = mentorId;
                            mentorSubjects.Save();
                        }
                        ts.Complete();
                    }

                    return true;
                }
            }
            catch (Exception ex)
            {
            }
            return false;
        }

        public List<SubjectCompactViewModel> GetSubjects(int pageIndex, int pageSize, int userid, SubjectListFilterViewModel parameters, out int totalCount, bool count = true)
        {

            SubjectListFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<SubjectCompactViewModel> subjectViewList = new List<SubjectCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT *");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   subjects s");
                sql.AppendLine(@"WHERE  1 = 1");

                if (userid > 0)
                {
                    sql.AppendLine(@"       AND s.subject_id NOT IN (SELECT s.subject_id");
                    sql.AppendLine(@"                                FROM   subjects_enrolled se");
                    sql.AppendLine(@"                                       LEFT JOIN subjects s");
                    sql.AppendLine(@"                                         ON se.subject_id = s.subject_id");
                    sql.AppendLine(@"                                WHERE  1 = 1");
                    sql.AppendLine(@"                                       AND se.user_id = ?UserId)");
                    //TODO add cancel request if needed
                    //sql.AppendLine(@"                                       AND se.enabled = 1);  ");

                }

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?UserId", DbType.Int32, userid);

                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                subjectViewList = studentsTable.AsEnumerable().Select(datarow => new SubjectCompactViewModel
                {
                    SubjectCode = datarow["subject_code"].ToString(),
                    SubjectId = datarow["subject_id"].ToInteger(),
                    SubjectName = datarow["subject_name"].ToString()
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return subjectViewList;
            }
            catch (Exception ex)
            { }
            return subjectViewList;
        }

        public bool DeleteSubject(int id)
        {
            try
            {
                SubjectBase.Delete(x => x.SubjectId == id);
                LessonsSubjectBase.Delete(x => x.SubjectId == id);
                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public AdminSubjectsViewModel GetSubjectBySubjectId(int id)
        {
            AdminSubjectsViewModel adminSubjectsViewModel = new AdminSubjectsViewModel();
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT *  ");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM subjects s ");

                sql.Append(" WHERE 1 = 1 ");
                sql.Append(" AND s.subject_id = ?SubjectId ");


                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, id);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                adminSubjectsViewModel.SubjectCode = studentsTable.Rows[0]["subject_code"].ToString();
                adminSubjectsViewModel.SubjectId = studentsTable.Rows[0]["subject_id"].ToInteger();
                adminSubjectsViewModel.SubjectName = studentsTable.Rows[0]["subject_name"].ToString();

                return adminSubjectsViewModel;

            }
            catch (Exception ex)
            {
                throw;
            }
            return adminSubjectsViewModel;
        }

        public Boolean SetSubjectEnrolled(int subjectId, int mentorId, int userId)
        {
            try
            {
                SubjectsEnrolled subjectsEnrolled = new SubjectsEnrolled();
                subjectsEnrolled.UserId = userId;
                subjectsEnrolled.SubjectId = subjectId;
                subjectsEnrolled.MentorId = mentorId;
                subjectsEnrolled.Enabled = false;
                subjectsEnrolled.Save();
                return true;
            }
            catch (Exception ex)
            {
                throw;
            }
            return false;
        }

        public void ApproveSubjecEnroll(int subjectEnrolledId)
        {
            try
            {
                SubjectsEnrolled subjectsEnrolled = db.Select.From<SubjectsEnrolled>()
                    .Where(SubjectsEnrolledTable.SubjectEnrolledIdColumn).IsEqualTo(subjectEnrolledId).ExecuteSingle<SubjectsEnrolled>();
                subjectsEnrolled.Enabled = true;
                subjectsEnrolled.Update();
            }
            catch (Exception)
            {

                throw;
            }

        }

        public List<SubjectCompactViewModel> GetSubjectsByMentorId(int pageIndex, int pageSize, int userId, SubjectListFilterViewModel parameters, out int totalCount, bool count = true)
        {

            SubjectListFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<SubjectCompactViewModel> subjectViewList = new List<SubjectCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();


                sql.AddStartRemove();
                sql.AppendLine(@"SELECT s.subject_id,");
                sql.AppendLine(@"       s.subject_code,");
                sql.AppendLine(@"       s.subject_name,");
                sql.AppendLine(@"       m.mentor_id,");
                sql.AppendLine(@"       (SELECT COUNT(*)");
                sql.AppendLine(@"        FROM   subjects_enrolled se");
                sql.AppendLine(@"        WHERE  se.subject_id = s.subject_id");
                sql.AppendLine(@"               AND se.mentor_id = m.mentor_id");
                sql.AppendLine(@"               AND enabled = 0) as pending_request,");
                sql.AppendLine(@"       (SELECT COUNT(*)");
                sql.AppendLine(@"        FROM   subjects_enrolled se");
                sql.AppendLine(@"        WHERE  se.subject_id = s.subject_id");
                sql.AppendLine(@"               AND se.mentor_id = m.mentor_id");
                sql.AppendLine(@"               AND enabled = 1) as enrolled");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM mentors_subjects ms");
                sql.AppendLine(@"JOIN subjects s ON s.subject_id = ms.subject_id");
                sql.AppendLine(@"LEFT JOIN mentors m ON ms.mentor_id = m.mentor_id");

                sql.Append(" WHERE 1 = 1 ");

                sql.AppendLine(@"AND m.user_id = ?UserId");

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?UserId", DbType.Int32, userId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                subjectViewList = studentsTable.AsEnumerable().Select(datarow => new SubjectCompactViewModel
                {
                    SubjectCode = datarow["subject_code"].ToString(),
                    SubjectId = datarow["subject_id"].ToInteger(),
                    SubjectName = datarow["subject_name"].ToString(),
                    PendingEnrollment = datarow["pending_request"].ToInteger(),
                    Enrolled = datarow["enrolled"].ToInteger()
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return subjectViewList;
            }
            catch (Exception ex)
            { }
            return subjectViewList;
        }

        public List<SubjectCompactViewModel> GetSubjectsByStudentId(int pageIndex, int pageSize, int userId, SubjectListFilterViewModel parameters, out int totalCount, bool count = true)
        {

            SubjectListFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<SubjectCompactViewModel> subjectViewList = new List<SubjectCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT s.subject_id,");
                sql.AppendLine(@"       s.subject_code,");
                sql.AppendLine(@"       s.subject_name,");
                sql.AppendLine(@"       m.user_id AS mentor_user_id");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   subjects_enrolled se");
                sql.AppendLine(@"       LEFT JOIN subjects s");
                sql.AppendLine(@"         ON se.subject_id = s.subject_id");
                sql.AppendLine(@"       LEFT JOIN mentors m");
                sql.AppendLine(@"         ON se.mentor_id = m.mentor_id");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND se.user_id = ?UserId");
                sql.AppendLine(@"       AND se.enabled = 1  ");


                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?UserId", DbType.Int32, userId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                subjectViewList = studentsTable.AsEnumerable().Select(datarow => new SubjectCompactViewModel
                {
                    SubjectCode = datarow["subject_code"].ToString(),
                    SubjectId = datarow["subject_id"].ToInteger(),
                    SubjectName = datarow["subject_name"].ToString(),
                    MentorUserId = datarow["mentor_user_id"].ToInteger(),
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return subjectViewList;
            }
            catch (Exception ex)
            { }
            return subjectViewList;
        }

    }
}